import csv
import datetime
from io import StringIO

from flask import jsonify, request, Response, render_template

from tzq.database_compare_system import dcs_bp
from tzq.database_compare_system.models.dcs_common_models import TableDefine
from tzq.database_compare_system.models.dcs_task_models import DatabaseInfo
from tzq.database_compare_system.services.table_define_sql import get_table_define_sql
from tzq.extensions import db
from tzq.util.ExecuteBatchSQLScripts import ExecuteBatchSQLScripts


@dcs_bp.route('/api/initTableDefineData/<int:id>', methods=['GET'])
def init_table_define_data(id):
    """
    初始化 源端表结构ddl脚本 的相关信息
    根据数据源的信息，把 源端表结构ddl脚本 信息通过数据链dblink查出来的数据插入到 dc_table_define_t 表
    """
    database_info = DatabaseInfo.query.get_or_404(id)
    try:
        connect_name = database_info.connect_name
        database_type = database_info.database_type
        host = database_info.host
        port = str(database_info.port)
        dbname = database_info.dbname
        username = database_info.username
        password = database_info.password
        v_table_define_sql = get_table_define_sql(database_type, connect_name)
        v_sql = """
            delete from dc_table_define_t where source = '""" + connect_name + """';
            INSERT INTO dc_table_define_t (source, schema, owner, table_name, table_define, table_creator, created, last_ddl_time)
            select source, schema, owner, table_name, table_define, table_creator
                  ,created::timestamp(0)
                  ,last_ddl_time::timestamp(0)
              from dblink('host=""" + host + """ dbname=""" + dbname + """ port=""" + port + """ user=""" + username + """ password=""" + password + """',
            '
            """ + v_table_define_sql + """
            ')
            as t (
              source text,
              schema text,
              owner text,
              table_name text,
              table_define text,
              table_creator text,
              created text,
              last_ddl_time text
            );
            """
        executor = ExecuteBatchSQLScripts(db)
        v_result = executor.execute_sql_json(v_sql)
        v_result = v_result.get_json()
        if v_result['code'] == 1:
            return jsonify({"code": 1, "msg": "【OK】"})
        elif v_result['code'] == 0:
            return jsonify({"code": -1, "msg": "【ERROR】" + v_result['msg']})
    except Exception as e:
        return jsonify({"code": 0, "msg": str(e), "error": str(e)}), 500


@dcs_bp.route('/api/tableDefines', methods=['GET', 'POST'])
def table_defines():
    """ 数据接口 - 支持分页 """
    page = int(request.args.get('page', 1))
    limit = int(request.args.get('limit', 10))
    # 获取搜索参数
    source = request.values.get("source")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    table_define = request.values.get("table_define")

    # 基础查询
    query = TableDefine.query

    # 应用搜索条件
    if source:
        query = query.filter(TableDefine.source.ilike(f'%{source}%'))
    if schema:
        query = query.filter(TableDefine.schema.ilike(f'%{schema}%'))
    if table_name:
        query = query.filter(TableDefine.table_name.ilike(f'%{table_name}%'))
    if table_define:
        query = query.filter(TableDefine.table_define.ilike(f'%{table_define}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'creation_date')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(TableDefine, sort_field):
        if sort_order == 'asc':
            order_by = getattr(TableDefine, sort_field).asc()
        else:
            order_by = getattr(TableDefine, sort_field).desc()
        query = query.order_by(order_by)
    else:
        query = query.order_by(TableDefine.table_define_id.desc())

    # 分页查询
    pagination = query.paginate(page=page, per_page=limit, error_out=False)

    # 构建Layui要求的JSON格式
    return jsonify({
        'code': 0,
        'msg': '',
        'count': pagination.total,
        'data': [tableDefine.to_dict() for tableDefine in pagination.items]
    })


@dcs_bp.route('/api/exportTableDefines', methods=['GET', 'POST'])
def export_table_defines():
    """ 【建表DDL脚本】数据导出 """
    # 获取搜索参数 , POST方法 用 form.get
    source = request.values.get("source")
    schema = request.values.get("schema")
    table_name = request.values.get("table_name")
    table_define = request.values.get("table_define")
    # 获取所有数据
    data = TableDefine.query

    # 应用搜索条件
    if source:
        data = data.filter(TableDefine.source.ilike(f'%{source}%'))
    if schema:
        data = data.filter(TableDefine.schema.ilike(f'%{schema}%'))
    if table_name:
        data = data.filter(TableDefine.table_name.ilike(f'%{table_name}%'))
    if table_define:
        data = data.filter(TableDefine.table_define.ilike(f'%{table_define}%'))

    # 获取排序参数
    sort_field = request.args.get('field', 'table_define_id')
    sort_order = request.args.get('order', 'desc')

    # 构建排序
    if hasattr(TableDefine, sort_field):
        if sort_order == 'asc':
            order_by = getattr(TableDefine, sort_field).asc()
        else:
            order_by = getattr(TableDefine, sort_field).desc()
        data = data.order_by(order_by)
    else:
        data = data.order_by(TableDefine.index_info_id.desc())

    # 分页查询
    data = data.paginate(page=1, per_page=1000000, error_out=False)

    # 创建内存文件对象
    output = StringIO()
    output.write('\ufeff')  # 添加UTF-8 BOM头

    # 创建CSV写入器
    writer = csv.writer(output, quoting=csv.QUOTE_ALL)

    # 写入表头
    headers = [
        'ID', '来源', 'schema', '属主', '表名', '表定义ddl脚本', '表的创建者', '源表创建时间', '源表最后ddl时间',
        '数据拉取时间'
    ]  # 使用中文表头
    writer.writerow(headers)

    # 写入数据行
    for item in data:
        row = [
            item.table_define_id,
            item.source,
            item.schema,
            item.owner,
            item.table_name,
            item.table_define,
            item.table_creator,
            item.created,
            item.last_ddl_time,
            item.creation_date,
            # 添加其他字段...
        ]
        writer.writerow(row)

    date_string = datetime.datetime.now().strftime("%Y%m%d-%H%M%S")

    # 创建响应对象
    response = Response(
        output.getvalue(),
        mimetype="text/csv; charset=utf-8",
        headers={
            "Content-disposition": "attachment; filename=" + date_string + "-table_define_data.csv",
            "Content-type": "text/csv; charset=utf-8"
        }
    )
    return response


@dcs_bp.route('/api/tableDefineGetSources', methods=['GET'])
def table_define_get_source_data():
    """
    从PostgreSQL获取 source 下拉框的数据，提供json数据的api接口
    【建表DDL脚本】查询页面 的 【数据源】下拉框 展示
    """
    try:
        # 查询去重后的 source 字段值
        distinct_sources = db.session.query(TableDefine.source).distinct().all()
        # 提取结果（因为返回的是元组列表）
        sources_data = [{"source": item[0]} for item in distinct_sources]  # 提取元组中的第一个元素
        return jsonify({"code": 0, "msg": "", "count": len(sources_data), "data": sources_data})
    except Exception as e:
        print("Database error:", e)
        return jsonify({"code": -1, "msg": str(e), "count": 0, "data": None})


# 页面跳转
@dcs_bp.route('/tableDefineCodeView', methods=['GET', 'POST'])
def table_define_code_view():
    if request.method == "GET":
        id_string = request.args.get("id")
        table_define = TableDefine.query.get_or_404(str(id_string))
    return render_template('table_define_code_view.html', content=table_define.table_define)


# ajax调用api
@dcs_bp.route('/api/getTableDefineCodeView/<int:id>', methods=['GET'])
def get_table_define_code_view(id):
    try:
        table_define = TableDefine.query.get_or_404(id)
        return jsonify({"code": 0, "msg": "", "data": table_define.to_dict()})
    except Exception as e:
        return jsonify({"code": -1, "msg": str(e)}), 500
